<html>

<head>
<title>SQL Relay - Getting Started With Oracle</title>
<link rel="stylesheet" href="../css/styles.css">
</head>

<body>

<span class="heading1">Getting Started With Oracle</span><br>

<ul>
<li><a href="#installation">Installation</a></li>
<ul>
<li><a href="#solaris">Solaris</a></li>
<li><a href="#linux">Linux</a></li>
<ul>
<li><a href="#users">Create Users, Groups and Directories</a></li>
<li><a href="#memory">Make Sure You Have Enough Memory</a></li>
<li><a href="#development">Install the Development Software</a></li>
<li><a href="#env">Set Up the Environment</a></li>
<li><a href="#oracle">Install Oracle</a></li>
<li><a href="#startingatboot">Starting the Database at Boot Time</a></li>
</ul>
</ul>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
</ul>

<a name="installation"></a>
<span class="heading1">Installation</span><br>

<p>I've sucessfully installed Oracle on Linux and I've
witnessed many Solaris installations.</p>

<p>When installing Oracle, the most common mistake I've made is having too 
little physical ram or swap.  The second most common mistake I've made is not 
configuring the kernel with enough shared memory or semaphore resources.  The 
installation instructions that come with Oracle for each platform indicate how 
much ram and swap you need and how to configure the kernel properly.  I 
recommend meeting these requirements prior to installation.</p>

<a name="solaris"></a>
<span class="heading2">Solaris</span><br>

<p>Oracle versions 8.1.5-10.2.0 for Solaris have been or are currently available
for download from <a href="http://technet.oracle.com">technet.oracle.com</a> 
or on CD.  Installation appears to be as simple as following the directions.  
Note that for Solaris, these versions of Oracle will fail to create a database 
at installation time unless the kernel is configured properly to support more 
and larger shared memory segments and semaphores.  Aside from neglecting this 
step, I've never seen or heard of anyone having much difficulty with these 
versions on Solaris.</p>

<a name="linux"></a>
<span class="heading2">Linux</span><br>

<p>Oracle has supported Linux since version 8.0.5. Versions 8.0.5 through 10.2.0
have been or are currently available from 
<a href="http://technet.oracle.com">technet.oracle.com</a> and have been 
available on CD from various sources as well.</p>

<a name="users"></a>
<span class="heading3">Create Users, Groups and Directories</span><br>

<p>Run the following commands as root to set up the appropriate users, groups
and directories.</p>

<blockquote><b>
groupadd oinstall<br>
groupadd dba<br>
groupadd oper<br>
useradd -m oracle -g oinstall -G dba,oper<br>
chown -R oracle:dba /home/oracle<br>
passwd oracle</b>  (assign the oracle user a password) <br>
<b>mkdir /u01<br>
chown oracle:dba /u01<br>
chmod 775 /u01<br>
</b></blockquote>

<p>Edit /home/oracle/.bashrc and add the following command near the bottom.</p>

<blockquote><b>
umask 022
</b></blockquote>

<a name="memory"></a>
<span class="heading3">Make Sure You Have Enough Memory</span><br>

<p>The number one reason that Oracle installations have failed for me is 
because I ran out of ram and swap.  Oracle really needs about 725mb of
total ram/swap.  If your system has less than that, you can add 725mb of swap
as follows.</p>

<blockquote>
<b>dd if=/dev/zero of=/extraswap bs=1k count=742400<br>
chmod 600 /extraswap<br>
/sbin/mkswap /extraswap<br>
/sbin/swapon /extraswap</b>
</blockquote>

<p>Oracle doesn't appear to need that much ram/swap to run, so after the
installation is finished, you can remove it as follows:</p>

<blockquote>
<b>/sbin/swapoff /extraswap<br>
rm /extraswap</b>
</blockquote>

<a name="development"></a>
<span class="heading3">Install the Development Software</span><br>

<p>The Oracle installer needs GNU make, the GNU C Compiler (gcc) and GNU
Binutils (ar, as, ld, etc.) to compile and link several libraries at
installation time.  Make sure that these packages are installed.</p>

<a name="env"></a>
<span class="heading3">Set Up the Environment</span><br>

<p>Edit /etc/profile (or /etc/bash.bashrc.local if your system has it) and add
the following somewhere near the top, replacing 10.2.0 in the ORACLE_VERSION
environment variable with the version of Oracle that you are installing.</p>

<blockquote><b>
export ORACLE_VERSION=10.2.0<br>
export ORACLE_BASE=/u01/app/oracle<br>
export ORACLE_HOME=$ORACLE_BASE/product/$ORACLE_VERSION<br>
export ORACLE_SID=ora1<br>
export PATH=$PATH:$ORACLE_HOME/bin<br>
export CLASSPATH=$ORACLE_BASE/jdbc/lib/classes111.zip<br>
export LD_LIBRARY_PATH=$ORACLE_HOME/lib<br>
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data<br>
export ORACLE_OWNER=oracle<br>
export ORACLE_TERM=386<br>
export NLS_LANG=american<br>
</b></blockquote>

<p>If you want to use a language other than US English, consult the following
table and replace <b>american</b> with the appropriate language string.</p>


<table border="1">
<tr>
<td><b><font face="Arial,Helvetica">Language</font></b></td>
<td><b><font face="Arial,Helvetica">NLS_LANG value</font></b></td>
</tr>
<tr><td>US English</td><td>american</td></tr>
<tr><td>Arabic</td><td>arabic</td></tr>
<tr><td>Brazilian Portugese</td><td>"brazilian portugese"</td></tr>
<tr><td>Canadian French</td><td>frc</td></tr>
<tr><td>Czech</td><td>czech</td></tr>
<tr><td>Danish</td><td>danish</td></tr>
<tr><td>Dutch</td><td>dutch</td></tr>
<tr><td>Finnish</td><td>finnish</td></tr>
<tr><td>French</td><td>french</td></tr>
<tr><td>German</td><td>german</td></tr>
<tr><td>Hungarian</td><td>hungarian</td></tr>
<tr><td>Icelandic</td><td>is</td></tr>
<tr><td>Italian</td><td>italian</td></tr>
<tr><td>Japanese</td><td>japanese</td></tr>
<tr><td>Korean</td><td>korean</td></tr>
<tr><td>Lithuanian</td><td>lt</td></tr>
<tr><td>Mexican Spanish</td><td>esm</td></tr>
<tr><td>Norweigan</td><td>norweigan</td></tr>
<tr><td>Polish</td><td>polish</td></tr>
<tr><td>Portugese</td><td>portugese</td></tr>
<tr><td>Russian</td><td>russian</td></tr>
<tr><td>Simplified Chinese</td><td>"simplified chinese"</td></tr>
<tr><td>Slovak</td><td>slovak</td></tr>
<tr><td>Swedish</td><td>swedish</td></tr>
<tr><td>Thai</td><td>th</td></tr>
<tr><td>Traditional Chinese</td><td>"traditional chinese"</td></tr>
<tr><td>Turkish</td><td>turkish</td></tr>
</table>

<br>

<a name="oracle"></a>
<span class="heading3">Install Oracle</span><br>

<p>At this point, you should be ready to install Oracle.</p>

<p>Each version of Oracle has different installation procedures.  Follow the
links below for the version of Linux and Oracle that you are installing.  Each 
"Yes" link indicates that I was able to get that version of Oracle to install
and run on that version of Linux.</p>

<table border="1">
<tr>
<td></td>
<th>8.0.5</th>
<th>8.1.5</th>
<th>8.1.6</th>
<th>8.1.7</th>
<th>9.0.x</th>
<th>9.2.x</th>
<th>10.1.x</th>
<th>10.2.x</th>
<th>11.1.0</th>
</tr>
<tr>
<th>Redhat 5.2</th>
<td><a href="oracle/linux/redhat/5/805.html">Yes</a></td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Redhat 6.x</th>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/redhat/6/8i.html">Yes</a></td>
<td><a href="oracle/linux/redhat/6/8i.html">Yes</a></td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Redhat 7.x</th>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/redhat/7/8i.html">Yes</a></td>
<td><a href="oracle/linux/redhat/7/8i.html">Yes</a></td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Redhat 8</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/redhat/8/9iR2.html">Yes</a></td>
<td>No</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Redhat 9</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/redhat/9/9iR2.html">Yes</a></td>
<td>No</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 1</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/fedoracore/1/9iR2.html">Yes</a></td>
<td><a href="oracle/linux/fedoracore/1/10g.html">Yes</a></td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 2</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/fedoracore/2/10g.html">Yes</a></td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 3</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/fedoracore/3/10g.html">Yes</a></td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 4</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<!-- directions for fc3 work for fc4 -->
<td><a href="oracle/linux/fedoracore/3/10g.html">Yes</a></td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 5</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/fedoracore/5/10g.html">Yes</a></td>
<td>No</td>
</tr>
<tr>
<th>Fedora Core 9</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/fedoracore/9/11g.html">Yes</a></td>
</tr>
<tr>
<th>Suse 9.0</th>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td>No</td>
<td><a href="oracle/linux/suse/9.0/9iR2.html">Yes</a></td>
<td>No</a></td>
<td>No</td>
<td>No</td>
</tr>
</table>
<br>

<a name="startingatboot"></a>
<span class="heading3">Starting the Database at Boot Time</span><br>

<p>For Oracle versions 10g or earlier, you can use the following script to start/stop the database at boot/shutdown time.  Replace /u01/app/oracle/product/10.2.0 with your $ORACLE_HOME</p>

<blockquote>
<PRE>
<FONT color=#0000ff>#!/bin/sh</FONT>

<B><FONT color=#a62828>case</FONT></B> &quot;$1&quot; <B><FONT color=#a62828>in</FONT></B>
  start<B><FONT color=#a62828>)</FONT></B>
        su <B><FONT color=#a62828>-l</FONT></B> oracle <B><FONT color=#a62828>-c</FONT></B> <B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>/u01/app/oracle/product/10.2.0/bin/dbstart</FONT><B><FONT color=#a62828>&quot;</FONT></B>
        su <B><FONT color=#a62828>-l</FONT></B> oracle <B><FONT color=#a62828>-c</FONT></B> <B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>/u01/app/oracle/product/10.2.0/bin/lsnrctl start</FONT><B><FONT color=#a62828>&quot;</FONT></B>
        <B><FONT color=#a62828>;;</FONT></B>
  stop<B><FONT color=#a62828>)</FONT></B>
        su <B><FONT color=#a62828>-l</FONT></B> oracle <B><FONT color=#a62828>-c</FONT></B> <B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>/u01/app/oracle/product/10.2.0/bin/dbshut</FONT><B><FONT color=#a62828>&quot;</FONT></B>
        su <B><FONT color=#a62828>-l</FONT></B> oracle <B><FONT color=#a62828>-c</FONT></B> <B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>/u01/app/oracle/product/10.2.0/bin/lsnrctl stop</FONT><B><FONT color=#a62828>&quot;</FONT></B>
        <B><FONT color=#a62828>;;</FONT></B>
  *<B><FONT color=#a62828>)</FONT></B>
        <B><FONT color=#a62828>echo</FONT></B><FONT color=#ff00ff> $</FONT><B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>Usage: </FONT><FONT color=#a620f7>$0</FONT><FONT color=#ff00ff> {start|stop}</FONT><B><FONT color=#a62828>&quot;</FONT></B>
        <B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>1</FONT>
<B><FONT color=#a62828>esac</FONT></B>

<B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>0</FONT>

</PRE>
</blockquote>

<p>For Oracle 11g or later, use the following script, replacing /u01/app/oracle/product/11.1.0 with your $ORACLE_HOME</p>

<blockquote>
<font color="#0000ff">#!/bin/sh</font><br>
<br>
<font color="#008b8b">ORACLE_HOME</font>=/u01/app/oracle/product/<font color="#ff00ff">11</font>.<font color="#ff00ff">1</font>.<font color="#ff00ff">0</font><br>
<br>
<font color="#a52a2a"><b>case</b></font>&nbsp;<font color="#a52a2a"><b>&quot;</b></font><font color="#a020f0">$1</font><font color="#a52a2a"><b>&quot;</b></font>&nbsp;<font color="#a52a2a"><b>in</b></font><br>
&nbsp;&nbsp;start<font color="#a52a2a"><b>)</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;su -l oracle -c <font color="#a52a2a"><b>&quot;</b></font><font color="#a020f0">$ORACLE_HOME</font><font color="#ff00ff">/bin/dbstart </font><font color="#a020f0">$ORACLE_HOME</font><font color="#a52a2a"><b>&quot;</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#a52a2a"><b>;;</b></font><br>
&nbsp;&nbsp;stop<font color="#a52a2a"><b>)</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;su -l oracle -c <font color="#a52a2a"><b>&quot;</b></font><font color="#a020f0">$ORACLE_HOME</font><font color="#ff00ff">/bin/dbshut </font><font color="#a020f0">$ORACLE_HOME</font><font color="#a52a2a"><b>&quot;</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#a52a2a"><b>;;</b></font><br>
&nbsp;&nbsp;*<font color="#a52a2a"><b>)</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#a52a2a"><b>echo</b></font><font color="#ff00ff">&nbsp;$</font><font color="#a52a2a"><b>&quot;</b></font><font color="#ff00ff">Usage: </font><font color="#a020f0">$0</font><font color="#ff00ff">&nbsp;{start|stop}</font><font color="#a52a2a"><b>&quot;</b></font><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#a52a2a"><b>exit</b></font>&nbsp;<font color="#ff00ff">1</font><br>
<font color="#a52a2a"><b>esac</b></font><br>
<br>
<font color="#a52a2a"><b>exit</b></font>&nbsp;<font color="#ff00ff">0</font><br>
</font>
</blockquote>

<p>Install this script and run it with the "start" option to start up the
database.  Running it with the "stop" option shuts the database down.  To
access a database, it must be running.</p>

<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>

<p>During the installation, you could have chosen to create a database instance.
If you did not create one during installation, you can do so afterward. Log in
as oracle and run $ORACLE_HOME/bin/dbassist for Oracle 8i or
$ORACLE_HOME/bin/dbca for Oracle 9i or higher and follow the on-screen
instructions.</p>

<p>The dbassist/dbca program will ask you to provide an SID; a local identifier
for the database.  No two databases on the same machine can have the same SID. 
If you are using Oracle 8i or 9i you will also be asked to provide a Global 
Identifier.  The Global Identifier should be unique among the Oracle databases 
on the local network.</p>

<p>After creating the database, edit the file /etc/oratab and look for a line
containing the SID of the database you just created.  Change the N at the end 
of that line to Y.  This configures the database to start when the dbstart 
command is issued.</p>

<p>At this point, you can run the start/stop script to start up the 
database.</p>

<p>To access the database, the file $ORACLE_HOME/network/admin/tnsnames.ora 
must also be configured.  This file contains an entry for each database that 
the local system needs to know about.  Here is an example of a tnsnames.ora 
entry, identifying the database <i>ORA1</i> on the local machine.</p>

<blockquote>
<PRE>
ORA1.LOCALDOMAIN =
  (<B><FONT color=#288a51>DESCRIPTION</FONT></B> =
    (<B><FONT color=#288a51>ADDRESS_LIST</FONT></B> =
      (<B><FONT color=#288a51>ADDRESS</FONT></B> = (<B><FONT color=#a62828>PROTOCOL</FONT></B> = <B><FONT color=#a62828>TCP</FONT></B>)(<B><FONT color=#a62828>HOST</FONT></B> = localhost)(<B><FONT color=#a62828>PORT</FONT></B> = 1521))
    )
    (<B><FONT color=#288a51>CONNECT_DATA</FONT></B> =
      (<B><FONT color=#a62828>SERVICE_NAME</FONT></B> = ora1)
    )
  )
</PRE>
</blockquote>

<p>For Oracle 7 and 8, the format is SID = (... parameters ...).  For Oracle 8i
and 9i, the format is SID.DOMAIN = (... parameters ...). In this example
the SID is ORA1.  The HOST parameter refers to the DNS name of the host that 
the database is running on.  The SERVICE_NAME refers to the SID of the database 
as it is known on that host.</p>

<p>When a database instance is created, two special administration users are 
created: SYS and SYSTEM. For versions of Oracle less than 9i, the default
password for the SYSTEM user is <i>manager</i>.  The default password for the
SYS user is <i>change_on_install</i>.  For Oracle 9i passwords must be set
during the installation process.  These users own the tables that contain
privileges and other housekeeping information.</p>

<p>Database tables are stored in a file or set of files collectively called a
tablespace.  The tables owned by the SYS and SYSTEM users are kept in their
own tablespace.  Though you could create your own tables there, it's not a 
good idea.  You should create your own tablespace.</p>

<p>First, check to see if <i>/u01/app/oracle/oradata</i> and
<i>/u01/app/oracle/oradata/ora1</i> exist.  If either does not, run one or
both of the following commands to create the necessary directory:</p>

<blockquote><b>
mkdir -p /u01/app/oracle/oradata<br>
chown oracle:oinstall /u01/app/oracle/oradata<br><br>
mkdir -p /u01/app/oracle/oradata/ora1<br>
chown oracle:oinstall /u01/app/oracle/oradata/ora1
</b></blockquote>

<p>Now, for versions of Oracle less than 9i, use the following command to log
into the ORA1 instance as the SYSTEM user:</p>

<blockquote>
<b>sqlplus system/manager@ora1</b>
</blockquote>

<p>For Oracle 9i, use the same command, but replace <b>manager</b> with the
password you entered during installation.</p>

<p>For Oracle 10g or higher, use the following command to log into the ORA1
instance as the SYS user, replacing <b><i>password</i></b> with the password
you entered during installation:</p>

<blockquote>
<b>sqlplus sys/<i>password</i>@ora1 as sysdba</b>
</blockquote>

<p>The following queries
create a tablespace and temporary tablespace called <i>testtablespace</i> and 
<i>testtablespacetemp</i> with some minimal sizing parameters.</p>

<p>For versions of Oracle less than 8i, use the following queries:</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>CREATE</FONT></B> TABLESPACE testtablespace
	DATAFILE <FONT color=#ff00ff>'/u01/app/oracle/oradata/ora1/testtablespace01.dbf'</FONT> 
	<FONT color=#6959cf>SIZE</FONT> 1M REUSE
	AUTOEXTEND <FONT color=#6959cf>ON</FONT> NEXT 1M
	MAXSIZE UNLIMITED
	NOLOGGING;

<B><FONT color=#a62828>CREATE</FONT></B> TABLESPACE testtablespacetemp
	DATAFILE <FONT color=#ff00ff>'/u01/app/oracle/oradata/ora1/testtablespacetemp01.dbf'</FONT> 
	<FONT color=#6959cf>SIZE</FONT> 2M REUSE
	AUTOEXTEND <FONT color=#6959cf>ON</FONT> NEXT 1M
	MAXSIZE UNLIMITED;
</PRE>
</blockquote>

<p>For versions of Oracle equal to or greater than 8i, use the following
queries:</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>CREATE</FONT></B> TABLESPACE testtablespace
	DATAFILE <FONT color=#ff00ff>'/u01/app/oracle/oradata/ora1/testtablespace01.dbf'</FONT> 
	<FONT color=#6959cf>SIZE</FONT> 1M REUSE
	AUTOEXTEND <FONT color=#6959cf>ON</FONT> NEXT 1M
	MAXSIZE UNLIMITED
	NOLOGGING;

<B><FONT color=#a62828>CREATE</FONT></B> TEMPORARY TABLESPACE testtablespacetemp
	TEMPFILE <FONT color=#ff00ff>'/u01/app/oracle/oradata/ora1/testtablespacetemp01.dbf'</FONT> 
	<FONT color=#6959cf>SIZE</FONT> 2M REUSE
	AUTOEXTEND <FONT color=#6959cf>ON</FONT> NEXT 1M
	MAXSIZE UNLIMITED;
</PRE>
</blockquote>

<p>The set of database objects (such as tables, indexes, stored procedures and 
triggers) owned by a particular user is called a schema.  Though schemas may be
distributed across tablespaces, they are more often confined to a single 
tablespace.  The SYSTEM and SYS schemas contain tables used by internal systems
and should not be used for application data.  You can create a new user and 
schema by logging in as SYSTEM and running the following queries:</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>CREATE</FONT></B> <FONT color=#6959cf>USER</FONT> testuser <FONT color=#6959cf>IDENTIFIED</FONT> <FONT color=#6959cf>BY</FONT> testpassword
	<FONT color=#6959cf>DEFAULT</FONT> TABLESPACE testtablespace 
	TEMPORARY TABLESPACE testtablespacetemp;

<B><FONT color=#a62828>GRANT</FONT></B> <B><FONT color=#a62828>CREATE</FONT></B> <FONT color=#6959cf>SESSION</FONT> <FONT color=#6959cf>TO</FONT> testuser;

<B><FONT color=#a62828>GRANT</FONT></B> <FONT color=#6959cf>RESOURCE</FONT> <FONT color=#6959cf>TO</FONT> testuser;
</PRE>
</blockquote>

<p>Now you can exit and log in as the new user with the following command:</p>

<blockquote>
<b>sqlplus testuser/testpassword@ora1</b>
</blockquote>

<p>To delete a user and everything owned by that user, log in as SYSTEM and run
the following command:</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>DROP</FONT></B> <FONT color=#6959cf>USER</FONT> testuser CASCADE;
</PRE>
</blockquote>

<p>To delete a tablespace, log in as SYSTEM and run the following command:</p>

<blockquote>
<PRE>
<B><FONT color=#a62828>DROP</FONT></B> <FONT color=#6959cf>TABLESPACE</FONT> testtablespace;

<B><FONT color=#a62828>DROP</FONT></B> <FONT color=#6959cf>TABLESPACE</FONT> testtablespacetemp;
</PRE>
</blockquote>

<p>This should be enough you get you started.  To set up more complex 
configurations, consult the documentation that came with your Oracle 
distribution.</p>

<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>


<p>Any user can access an Oracle database provided that the users environment
is configured correctly.  The ORACLE_HOME environment variable needs to be set 
to the same value that it was set for the oracle user during installation.  The
PATH varible must contain $ORACLE_HOME/bin and the LD_LIBRARY_PATH variable 
must contain $ORACLE_HOME/lib.</p>

<p>The user must have read access to the
$ORACLE_HOME/network/admin/tnsnames.ora file.  Alternatively, an individual 
user can have a .tnsnames.ora file.</p>

<p>The following command logs <i>testuser</i> with password <i>testpassword</i>
into the database identified by the SID <i>ora1</i>.</p>

<blockquote>
<b>sqlplus testuser/testpassword@ora1</b>
</blockquote>

<p>A sample session follows.</p>

<blockquote>
<PRE>
[user@localhost user]$ <b>sqlplus testuser/testpassword@ora1</b>

SQL*Plus: Release <FONT color=#ff00ff>8.1.7.0.0</FONT> - Production on Tue Jan <FONT color=#ff00ff>8</FONT> <FONT color=#ff00ff>18</FONT>:<FONT color=#ff00ff>44</FONT>:<FONT color=#ff00ff>46</FONT> <FONT color=#ff00ff>2002</FONT>

(c) Copyright <FONT color=#ff00ff>2001</FONT> Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release <FONT color=#ff00ff>8.1</FONT>.<FONT color=#ff00ff>7.0</FONT>.<FONT color=#ff00ff>0</FONT> - Production
JServer Release <FONT color=#ff00ff>8.1</FONT>.<FONT color=#ff00ff>7.0</FONT>.<FONT color=#ff00ff>0</FONT> - Production

SQL&gt; <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
  <FONT color=#ff00ff>2</FONT>  col1 <B><FONT color=#288a51>varchar2</FONT></B>(<FONT color=#ff00ff>40</FONT>),
  <FONT color=#ff00ff>3</FONT>  col2 <B><FONT color=#288a51>number</FONT></B>
  <FONT color=#ff00ff>4</FONT>  );

Table created.

SQL&gt; <B><FONT color=#a62828>select</FONT></B> table_name <FONT color=#6959cf>from</FONT> user_tables;

TABLE_NAME
<FONT color=#0000ff>------------------------------</FONT>
TESTTABLE

SQL&gt; describe testtable;
 Name                                      <FONT color=#6959cf>Null</FONT>?    <FONT color=#6959cf>Type</FONT>
 <FONT color=#0000ff>----------------------------------------- -------- ----------------------------</FONT>
 COL1                                               <B><FONT color=#288a51>VARCHAR2</FONT></B>(<FONT color=#ff00ff>40</FONT>)
 COL2                                               <B><FONT color=#288a51>NUMBER</FONT></B>

SQL&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>);

<FONT color=#ff00ff>1</FONT> row created.

SQL&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>);

<FONT color=#ff00ff>1</FONT> row created.

SQL&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>);

<FONT color=#ff00ff>1</FONT> row created.

SQL&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;

COL1                                           COL2
<FONT color=#0000ff>---------------------------------------- ----------</FONT>
hello                                            <FONT color=#ff00ff>50</FONT>
hi                                               <FONT color=#ff00ff>60</FONT>
bye                                              <FONT color=#ff00ff>70</FONT>

SQL&gt; <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>;

<FONT color=#ff00ff>1</FONT> row deleted.

SQL&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;

COL1                                           COL2
<FONT color=#0000ff>---------------------------------------- ----------</FONT>
hi                                               <FONT color=#ff00ff>60</FONT>
bye                                              <FONT color=#ff00ff>70</FONT>

SQL&gt; <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>;

<FONT color=#ff00ff>1</FONT> row updated.

SQL&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;

COL1                                           COL2
<FONT color=#0000ff>---------------------------------------- ----------</FONT>
hi                                                <FONT color=#ff00ff>0</FONT>
bye                                              <FONT color=#ff00ff>70</FONT>

SQL&gt; <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable;

Table dropped.

SQL&gt; quit
Disconnected from Oracle8i Enterprise Edition Release <FONT color=#ff00ff>8.1</FONT>.<FONT color=#ff00ff>7.0</FONT>.<FONT color=#ff00ff>0</FONT> - Production
JServer Release <FONT color=#ff00ff>8.1</FONT>.<FONT color=#ff00ff>7.0</FONT>.<FONT color=#ff00ff>0</FONT> - Production

</PRE>
</blockquote>

<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>

<p>Accessing Oracle from SQL Relay requires an instance entry in your 
<i>sqlrelay.conf</i> file for the database that you want 
to access.  Here is an example <i>sqlrelay.conf</i> which defines an SQL Relay 
instance called oracletest.  This instance connects to the <i>ora1</i> 
database as the user <i>testuser</i> with password <i>testpassword</i>.</p>

<PRE>
<FONT color=#0000ff>&lt;?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>&quot;1.0&quot;</FONT><FONT color=#0000ff>?&gt;</FONT>
<FONT color=#008a8e>&lt;!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>&quot;sqlrelay.dtd&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
<FONT color=#008a8e>&lt;instances&gt;</FONT>

        <FONT color=#008a8e>&lt;instance id=</FONT><FONT color=#ff00ff>&quot;oracletest&quot;</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>&quot;9000&quot;</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>&quot;/tmp/oracletest.socket&quot;</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>&quot;oracle8&quot;</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>&quot;3&quot;</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>&quot;0&quot;</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>&quot;60&quot;</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>&quot;commit&quot;</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>&quot;600&quot;</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>&quot;listener&quot;</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>&quot;pass&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
                <FONT color=#008a8e>&lt;users&gt;</FONT>
                        <FONT color=#008a8e>&lt;user user=</FONT><FONT color=#ff00ff>&quot;oracletest&quot;</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>&quot;oracletest&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/users&gt;</FONT>
                <FONT color=#008a8e>&lt;connections&gt;</FONT>
                        <FONT color=#008a8e>&lt;connection connectionid=</FONT><FONT color=#ff00ff>&quot;oracletest&quot;</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>&quot;user=testuser;password=testpassword;oracle_sid=ora1&quot;</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/connections&gt;</FONT>
        <FONT color=#008a8e>&lt;/instance&gt;</FONT>

<FONT color=#008a8e>&lt;/instances&gt;</FONT>
</PRE>

<p>Now you can start up this instance with the following command.</p>

<blockquote>
<b>sqlr-start -id oracletest</b>
</blockquote>

<p>To connect to the instance and run queries, use the following command.</p>

<blockquote>
<b>sqlrsh -id oracletest</b>
</blockquote>

<p>The following command shuts down the SQL Relay instance.</p>

<blockquote>
<b>sqlr-stop oracletest</b>
</blockquote>

</body>

</html>
